package cn.itheima.student.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.sun.org.apache.bcel.internal.generic.NEW;

import cn.itheima.student.dao.StudentDao;
import cn.itheima.student.daomain.Student;
import cn.itheima.student.utils.JDBCUtils;

public class StudentDaoImpl implements StudentDao{

	public List<Student> findAll() {
		//创建runner 对象
		QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
		
		//2.编写sql
		String sql = "select * from student";
		
		//3.执行sql
		try {
			List<Student> list = runner.query(sql, new BeanListHandler<Student>(Student.class));
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<Student> findOne(int sid) {
		QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from student where sid = ?";
		try {
			List<Student> list = runner.query(sql, new BeanListHandler<Student>(Student.class) , sid);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public void addStudent(Student st) {
		
		QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
		
		String sql = "insert into student values(?,?,?,?,?,?,?)";
		//3.执行sql
		Object [] obj = {st.getSid(),st.getSname(),st.getSex(),st.getEmail(),st.getTelephone(),st.getAddr(),st.getAge()};
		
		try {
			int row = runner.update(sql, obj);
			if (row>0) {
				System.out.println("插入成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void updateStudent(Student st) {
		
		QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "update student set sname = ?,sex = ?,email = ?,telephone = ?,addr =?,age = ? where sid = ?";
		
		//执行sql
		Object [] obj = {st.getSname(),st.getSex(),st.getEmail(),st.getTelephone(),st.getAddr(),st.getAge(),st.getSid()};
		try {
			int row = runner.update(sql, obj);
			if (row>0) {
				System.out.println("更新成功");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	//每页的显示的数据
		public List<Student> findByPage(int begin, int pageSize) {
			//1.创建QueryRunner对象
					QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
					//2.编写sql
					String sql = "select * from student limit ?,?";
					//3.执行sql
					try {
						List<Student> list = runner.query(sql, new BeanListHandler<Student>(Student.class),begin,pageSize);
					    return list;
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					return null;
				}

	public Long findTotalCount() {
		QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
		
		String sql = "select count(*) from student ";
		
		try {
			Long dou = (Long) runner.query(sql, new ScalarHandler());
			
			return dou;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}

	public void deleteStudent(String sid) {
		QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
		
		String sql = "delete from student where sid = ?";
		try {
			int row = runner.update(sql, sid);
			if (row>0) {
				System.out.println("删除成功");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	public List<Student> findMoHu(String sname) {
		QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
		
		String sql= "select * from student where sname like ? ";
		try {
			List<Student> list = runner.query(sql, new BeanListHandler<Student>(Student.class),"%"+sname+"%");
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}


}
